package com.cm.cl;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Database {
	static String cnnStr;
	static Connection connection;

	static String dbName;
	static String host;

	static String password;

	static String port;

	static String tableName;

	static String username;

	// TODO: 检查可用性
	public static boolean check() {
		return true;
	}

	public static Connection getConnection() {
		if (connection == null) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				cnnStr = "jdbc:mysql://"
						+ getHost()
						+ ":"
						+ port
						+ "/"
						+ dbName
						+ "?useUnicode=true&characterEncoding=utf8&user="
						+ username
						+ ((password == null || password.length() == 0) ? ""
								: ("&password=" + password));
				connection = DriverManager.getConnection(cnnStr);

			} catch (Exception e) {

				e.printStackTrace();
				return null;
			}

		}
		return connection;

	}

	public static String getHost() {
		return host;
	}

	public static String getPassword() {
		return password;
	}

	public static String getPort() {
		return port;
	}

	public static String getTableName() {
		return tableName;
	}

	public static String getUsername() {
		return username;
	}

	public static boolean saveExcel(String excelFilePath) throws SQLException  {

		Workbook wb;
		StringBuilder sql = new StringBuilder();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		Statement stmt = null;
		try {
			wb = new HSSFWorkbook(new FileInputStream(excelFilePath));
			Sheet sheet = wb.getSheetAt(0);
			for (Row row : sheet) {
				if (row.getRowNum() == 0)
					continue;
				if (row.getCell(0) == null)
					break;
				sql.delete(0, sql.length());
				sql.append("INSERT INTO `"
						+ tableName
						+ "`(`id`,`department`, `category`, `source`, `task_name`, `detail`, `task_category`, `emergency`, `proposer`, `factor`, `handler`) VALUES (");
				//TODO:ID
				sql.append("'WL-"+sdf.format(new Date())+"-"+new Object().hashCode()+"',");
				for (Cell cell : row) {
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						sql.append("'" + cell.getStringCellValue() + "',");
						break;
					case Cell.CELL_TYPE_NUMERIC:
						sql.append("'" + cell.getNumericCellValue() + "',");
						break;
					default:
						break;
					}
				}
				sql.deleteCharAt(sql.lastIndexOf(","));
				sql.append(")");
				//System.out.println(row.getRowNum() + " " + sql.toString());
				stmt = connection.createStatement();
				stmt.execute(sql.toString());
			}
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(stmt!=null){
				stmt.close();
			}
		}
		return false;
	}

	public static void setHost(String host) {
		Database.host = host;
	}

	public static void setPassword(String password) {
		Database.password = password;
	}

	public static void setPort(String port) {
		Database.port = port;
	}

	public static void setTableName(String tableName) {
		Database.tableName = tableName;
	}

	public static void setUsername(String username) {
		Database.username = username;
	}

	public Database() {

	};

	public Database(String host, String username, String password, String port,
			String tableName) {
		super();
		Database.setHost(host);
		Database.username = username;
		Database.password = password;
		Database.port = port;
		Database.dbName = tableName;
	}

	public String getCnnStr() {
		return cnnStr;
	}

	public String getDbName() {
		return dbName;
	}

	public static void setDbName(String dbName) {
		Database.dbName = dbName;
	}

}
